﻿Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient

Public Class MySqlServiceImpl
    Implements IMySqlService

    Private Shared cn As New MySqlConnection

    Sub New()
        connection()
    End Sub

    Public Function connection() As MySqlConnection Implements IMySqlService.connection
        Return connection("Database=HC28;Data Source=192.168.37.129;Port=3306;User Id=administrator;Password=123456;Charset=utf8;TreatTinyAsBoolean=false;")
    End Function

    Public Function connection(ByRef dns As String) As MySqlConnection Implements IMySqlService.connection
        cn.ConnectionString = dns
        Return cn
    End Function

    Public Function [select](ByRef sql As String) As DataTable Implements IMySqlService.select
        Dim ad As New MySqlDataAdapter()
        Dim dt As DataTable = New DataTable
        Try
            'cn.Open()
            ad.SelectCommand() = New MySqlCommand(sql, cn)
            ad.Fill(dt)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'cn.Close()
        Return dt
    End Function

    Public Function update(ByRef sql As String) As Integer Implements IMySqlService.update
        Dim result As Integer
        Using cmd = New MySqlCommand(sql, cn)
            cn.Open()
            Try
                result = cmd.ExecuteNonQuery
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            cn.Close()
        End Using
        Return result
    End Function

    Public Function delete(ByRef sql As String) As Integer Implements IMySqlService.delete
        Dim result As Integer
        Using cmd = New MySqlCommand(sql, cn)
            cn.Open()
            Try
                result = cmd.ExecuteNonQuery
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            cn.Close()
        End Using
        Return result
    End Function

    Public Function insert(ByRef sql As String) As Integer Implements IMySqlService.insert
        Dim result As Integer
        Using cmd = New MySqlCommand(sql, cn)
            cn.Open()
            Try
                result = cmd.ExecuteNonQuery
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            cn.Close()
        End Using
        Return result
    End Function

    Public Function getValue(ByRef sql As String) As Object Implements IMySqlService.getValue
        Dim dt As DataTable = [select](sql)
        If dt.Rows.Count < 1 Then Return Nothing
        Return dt.Rows(0)(0)
    End Function
End Class
